Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# AGGCOUNT

The AGGCOUNT function retrieves the values of the Aggcount variable associated with the specified variable. An Aggcount variable is an `INTEGER` variable that Oracle OLAP automatically creates when it creates a variable defined using a DEFINE VARIABLE statement that includes a USING AGGOUNT phrase.

See also: "Aggcount Variables"

Return Value

`INTEGER`

The values of the Aggcount variable that are the non-`NA` counts of the number of leaf nodes that contribute to the calculation of aggregate values when RELATION (for aggregation) statements that have an AVERAGE, HAVERAGE, WAVERAGE, or HWAVERAGE execute.

Syntax

AGGCOUNT(variable-name)

Arguments

variable-name

  The name of the variable with which the Aggcount variable is associated.

Examples

Example 6-20 Reporting on an Aggcount Variable

Assume that within your analytic workspace you have objects with the following definitions.

    DEFINE geog DIMENSION TEXT
    DEFINE time DIMENSION TEXT
    DEFINE cc_geog_product COMPOSITE <geog product> COMPRESSED
    DEFINE sales VARIABLE DECIMAL <time cc_geog_product <geog product>> WITH AGGCOUNT
    DEFINE geog_parentrel RELATION geog <geog>
    DEFINE product_parentrel RELATION product <product>
    DEFINE time_parentrel RELATION time <time>
    DEFINE aggsales AGGMAP
       AGGMAP
       RELATION time_parentrel OPERATOR AVERAGE
       RELATION geog_parentrel
       RELATION product_parentrel
       END

Notice that the definition for the `sales` variable includes a request for an Aggcount variable and that, within the `aggsales` aggmap, the RELATION statement for the `time_parentrel` relation incudes an `AVERAGE` operator.

Assume also that when only the base values of the `sales` variable are populated, sales has the following values for Radios and TVs.

    REPORT sales

    PRODUCT: Radio
                   -------------SALES--------------
                   --------------TIME--------------
    GEOG              2004       2005     Average
    -------------- ---------- ---------- ----------
    Maine              122.93     176.69         NA
    California         168.32     150.92         NA
    Quebec                 NA         NA         NA
    Ontario            187.46     164.46         NA
    USA                    NA         NA         NA
    Canada                 NA         NA         NA
    World                  NA         NA         NA

    PRODUCT: TV
                   -------------SALES--------------
                   --------------TIME--------------
    GEOG              2004       2005     Average
    -------------- ---------- ---------- ----------
    Maine              184.75     135.40         NA
    California         139.89     145.71         NA
    Quebec                 NA         NA         NA
    Ontario            123.63     113.32         NA
    USA                    NA         NA         NA
    Canada                 NA         NA         NA
    World                  NA         NA         NA

    PRODUCT: AV
                   -------------SALES--------------
                   --------------TIME--------------
    GEOG              2004       2005     Average
    -------------- ---------- ---------- ----------
    Maine                  NA         NA         NA
    California             NA         NA         NA
    Quebec                 NA         NA         NA
    Ontario                NA         NA         NA
    USA                    NA         NA         NA
    Canada                 NA         NA         NA
    World                  NA         NA         NA

Since no aggregation has occurred, for AV, Oracle OLAP has not yet populated the Aggcount variable and the Aggcount variable for `sales` contains only `NA` values.

Now assume that you aggregate the `sales` variable by issuing the following statement.

    AGGREGATE sales USING aggsales

Once aggregated a report of `sales` shows the following values.

    REPORT sales

    PRODUCT: Radio
                   -------------SALES--------------
                   --------------TIME--------------
    GEOG              2004       2005     Average
    -------------- ---------- ---------- ----------
    Maine              122.93     176.69     149.81
    California         168.32     150.92     159.62
    Quebec                 NA         NA         NA
    Ontario            187.46     164.46     175.96
    USA                291.24     327.61     309.42
    Canada             187.46     164.46     175.96
    World              478.70     492.07     485.38

    PRODUCT: TV
                   -------------SALES--------------
                   --------------TIME--------------
    GEOG              2004       2005     Average
    -------------- ---------- ---------- ----------
    Maine              184.75     135.40     160.07
    California         139.89     145.71     142.80
    Quebec                 NA         NA         NA
    Ontario            123.63     113.32     118.47
    USA                324.64     281.11     302.87
    Canada             123.63     113.32     118.47
    World              448.27     394.42     421.35

    PRODUCT: AV
                   -------------SALES--------------
                   --------------TIME--------------
    GEOG              2004       2005     Average
    -------------- ---------- ---------- ----------
    Maine              307.67     312.08     309.88
    California         308.21     296.63     302.42
    Quebec                 NA         NA         NA
    Ontario            311.09     277.78     294.43
    USA                615.88     608.71     612.30
    Canada             311.09     277.78     294.43
    World              926.97     886.49     906.73

A report of the Aggcount variable shows that it is populated with the `INTEGER` values that are needed to aggregate the average sales.

    REPORT AGGCOUNT (sales)

    PRODUCT: Radio
                   --------AGGCOUNT (SALES)--------
                   --------------TIME--------------
    GEOG              2004       2005     Average
    -------------- ---------- ---------- ----------
    Maine                  NA         NA          2
    California             NA         NA          2
    Quebec                 NA         NA         NA
    Ontario                NA         NA          2
    USA                     2          2          4
    Canada                 NA         NA          2
    World                   3          3          6

    PRODUCT: TV
                   --------AGGCOUNT (SALES)--------
                   --------------TIME--------------
    GEOG              2004       2005     Average
    -------------- ---------- ---------- ----------
    Maine                  NA         NA          2
    California             NA         NA          2
    Quebec                 NA         NA         NA
    Ontario                NA         NA          2
    USA                     2          2          4
    Canada                 NA         NA          2
    World                   3          3          6

    PRODUCT: AV
                   --------AGGCOUNT (SALES)--------
                   --------------TIME--------------
    GEOG              2004       2005     Average
    -------------- ---------- ---------- ----------
    Maine                   2          2          4
    California              2          2          4
    Quebec                 NA         NA         NA
    Ontario                 2          2          4
    USA                     4          4          8
    Canada                  2          2          4
    World                   6          6         12